-- Drop Existing Procedures

IF OBJECT_ID(N'[dbo].[p_WM_PhieuGDChiTiet_Insert]') IS NOT NULL
	DROP PROCEDURE [dbo].[p_WM_PhieuGDChiTiet_Insert]

IF OBJECT_ID(N'[dbo].[p_WM_PhieuGDChiTiet_Update]') IS NOT NULL
	DROP PROCEDURE [dbo].[p_WM_PhieuGDChiTiet_Update]

IF OBJECT_ID(N'[dbo].[p_WM_PhieuGDChiTiet_InsertUpdate]') IS NOT NULL
	DROP PROCEDURE [dbo].[p_WM_PhieuGDChiTiet_InsertUpdate]

IF OBJECT_ID(N'[dbo].[p_WM_PhieuGDChiTiet_Delete]') IS NOT NULL
	DROP PROCEDURE [dbo].[p_WM_PhieuGDChiTiet_Delete]

IF OBJECT_ID(N'[dbo].[p_WM_PhieuGDChiTiet_DeleteDynamic]') IS NOT NULL
	DROP PROCEDURE [dbo].[p_WM_PhieuGDChiTiet_DeleteDynamic]

IF OBJECT_ID(N'[dbo].[p_WM_PhieuGDChiTiet_Load]') IS NOT NULL
	DROP PROCEDURE [dbo].[p_WM_PhieuGDChiTiet_Load]

IF OBJECT_ID(N'[dbo].[p_WM_PhieuGDChiTiet_SelectDynamic]') IS NOT NULL
	DROP PROCEDURE [dbo].[p_WM_PhieuGDChiTiet_SelectDynamic]

IF OBJECT_ID(N'[dbo].[p_WM_PhieuGDChiTiet_SelectAll]') IS NOT NULL
	DROP PROCEDURE [dbo].[p_WM_PhieuGDChiTiet_SelectAll]

IF OBJECT_ID(N'[dbo].[p_WM_PhieuGDChiTiet_SelectBy_Master_ID]') IS NOT NULL
	DROP PROCEDURE [dbo].[p_WM_PhieuGDChiTiet_SelectBy_Master_ID]

IF OBJECT_ID(N'[dbo].[p_WM_PhieuGDChiTiet_DeleteBy_Master_ID]') IS NOT NULL
	DROP PROCEDURE [dbo].[p_WM_PhieuGDChiTiet_DeleteBy_Master_ID]


GO


------------------------------------------------------------------------------------------------------------------------
-- Stored procedure name: [dbo].[p_WM_PhieuGDChiTiet_Insert]
-- Database: Warehouse_Management
-- Author: Ngo Thanh Tung
-- Time created: Sunday, November 04, 2012
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[p_WM_PhieuGDChiTiet_Insert]
	@Master_ID bigint,
	@Barcode varchar(20),
	@Ten nvarchar(2000),
	@DVT_ID char(5),
	@SoLuong decimal(18, 2),
	@DonGia decimal(18, 0),
	@ThanhTien decimal(18, 0),
	@isHangTra bit,
	@ID bigint OUTPUT
AS

INSERT INTO [dbo].[t_WM_PhieuGDChiTiet]
(
	[Master_ID],
	[Barcode],
	[Ten],
	[DVT_ID],
	[SoLuong],
	[DonGia],
	[ThanhTien],
	[isHangTra]
)
VALUES 
(
	@Master_ID,
	@Barcode,
	@Ten,
	@DVT_ID,
	@SoLuong,
	@DonGia,
	@ThanhTien,
	@isHangTra
)

SET @ID = SCOPE_IDENTITY()

GO

------------------------------------------------------------------------------------------------------------------------
-- Stored procedure name: [dbo].[p_WM_PhieuGDChiTiet_Update]
-- Database: Warehouse_Management
-- Author: Ngo Thanh Tung
-- Time created: Sunday, November 04, 2012
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[p_WM_PhieuGDChiTiet_Update]
	@ID bigint,
	@Master_ID bigint,
	@Barcode varchar(20),
	@Ten nvarchar(2000),
	@DVT_ID char(5),
	@SoLuong decimal(18, 2),
	@DonGia decimal(18, 0),
	@ThanhTien decimal(18, 0),
	@isHangTra bit
AS

UPDATE
	[dbo].[t_WM_PhieuGDChiTiet]
SET
	[Master_ID] = @Master_ID,
	[Barcode] = @Barcode,
	[Ten] = @Ten,
	[DVT_ID] = @DVT_ID,
	[SoLuong] = @SoLuong,
	[DonGia] = @DonGia,
	[ThanhTien] = @ThanhTien,
	[isHangTra] = @isHangTra
WHERE
	[ID] = @ID

GO

------------------------------------------------------------------------------------------------------------------------
-- Stored procedure name: [dbo].[p_WM_PhieuGDChiTiet_InsertUpdate]
-- Database: Warehouse_Management
-- Author: Ngo Thanh Tung
-- Time created: Sunday, November 04, 2012
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[p_WM_PhieuGDChiTiet_InsertUpdate]
	@ID bigint,
	@Master_ID bigint,
	@Barcode varchar(20),
	@Ten nvarchar(2000),
	@DVT_ID char(5),
	@SoLuong decimal(18, 2),
	@DonGia decimal(18, 0),
	@ThanhTien decimal(18, 0),
	@isHangTra bit
AS
IF EXISTS(SELECT [ID] FROM [dbo].[t_WM_PhieuGDChiTiet] WHERE [ID] = @ID)
	BEGIN
		UPDATE
			[dbo].[t_WM_PhieuGDChiTiet] 
		SET
			[Master_ID] = @Master_ID,
			[Barcode] = @Barcode,
			[Ten] = @Ten,
			[DVT_ID] = @DVT_ID,
			[SoLuong] = @SoLuong,
			[DonGia] = @DonGia,
			[ThanhTien] = @ThanhTien,
			[isHangTra] = @isHangTra
		WHERE
			[ID] = @ID
	END
ELSE
	BEGIN
		
		INSERT INTO [dbo].[t_WM_PhieuGDChiTiet]
		(
			[Master_ID],
			[Barcode],
			[Ten],
			[DVT_ID],
			[SoLuong],
			[DonGia],
			[ThanhTien],
			[isHangTra]
		)
		VALUES 
		(
			@Master_ID,
			@Barcode,
			@Ten,
			@DVT_ID,
			@SoLuong,
			@DonGia,
			@ThanhTien,
			@isHangTra
		)		
	END
GO

------------------------------------------------------------------------------------------------------------------------
-- Stored procedure name: [dbo].[p_WM_PhieuGDChiTiet_Delete]
-- Database: Warehouse_Management
-- Author: Ngo Thanh Tung
-- Time created: Sunday, November 04, 2012
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[p_WM_PhieuGDChiTiet_Delete]
	@ID bigint
AS

DELETE FROM 
	[dbo].[t_WM_PhieuGDChiTiet]
WHERE
	[ID] = @ID

GO

------------------------------------------------------------------------------------------------------------------------
-- Stored procedure name: [dbo].[p_WM_PhieuGDChiTiet_DeleteBy_Master_ID]
-- Database: Warehouse_Management
-- Author: Ngo Thanh Tung
-- Time created: Sunday, November 04, 2012
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[p_WM_PhieuGDChiTiet_DeleteBy_Master_ID]
	@Master_ID bigint
AS

DELETE FROM [dbo].[t_WM_PhieuGDChiTiet]
WHERE
	[Master_ID] = @Master_ID

GO

GO

------------------------------------------------------------------------------------------------------------------------
-- Stored procedure name: [dbo].[p_WM_PhieuGDChiTiet_DeleteDynamic]
-- Database: Warehouse_Management
-- Author: Ngo Thanh Tung
-- Time created: Sunday, November 04, 2012
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[p_WM_PhieuGDChiTiet_DeleteDynamic]
	@WhereCondition NVARCHAR(500)
AS

DECLARE @SQL NVARCHAR(MAX)

SET @SQL = 'DELETE FROM [dbo].[t_WM_PhieuGDChiTiet] WHERE ' + @WhereCondition

EXEC sp_executesql @SQL

GO

------------------------------------------------------------------------------------------------------------------------
-- Stored procedure name: [dbo].[p_WM_PhieuGDChiTiet_Load]
-- Database: Warehouse_Management
-- Author: Ngo Thanh Tung
-- Time created: Sunday, November 04, 2012
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[p_WM_PhieuGDChiTiet_Load]
	@ID bigint
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SELECT
	[ID],
	[Master_ID],
	[Barcode],
	[Ten],
	[DVT_ID],
	[SoLuong],
	[DonGia],
	[ThanhTien],
	[isHangTra]
FROM
	[dbo].[t_WM_PhieuGDChiTiet]
WHERE
	[ID] = @ID
GO

------------------------------------------------------------------------------------------------------------------------
-- Stored procedure name: [dbo].[p_WM_PhieuGDChiTiet_SelectBy_Master_ID]
-- Database: Warehouse_Management
-- Author: Ngo Thanh Tung
-- Time created: Sunday, November 04, 2012
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[p_WM_PhieuGDChiTiet_SelectBy_Master_ID]
	@Master_ID bigint
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SELECT
	[ID],
	[Master_ID],
	[Barcode],
	[Ten],
	[DVT_ID],
	[SoLuong],
	[DonGia],
	[ThanhTien],
	[isHangTra]
FROM
	[dbo].[t_WM_PhieuGDChiTiet]
WHERE
	[Master_ID] = @Master_ID

GO

------------------------------------------------------------------------------------------------------------------------
-- Stored procedure name: [dbo].[p_WM_PhieuGDChiTiet_SelectDynamic]
-- Database: Warehouse_Management
-- Author: Ngo Thanh Tung
-- Time created: Sunday, November 04, 2012
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[p_WM_PhieuGDChiTiet_SelectDynamic]
	@WhereCondition NVARCHAR(500),
	@OrderByExpression NVARCHAR(250) = NULL
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

DECLARE @SQL NVARCHAR(MAX)

SET @SQL = 
'SELECT 
	[ID],
	[Master_ID],
	[Barcode],
	[Ten],
	[DVT_ID],
	[SoLuong],
	[DonGia],
	[ThanhTien],
	[isHangTra]
FROM [dbo].[t_WM_PhieuGDChiTiet] 
WHERE ' + @WhereCondition

IF @OrderByExpression IS NOT NULL AND LEN(@OrderByExpression) > 0
BEGIN
	SET @SQL = @SQL + ' ORDER BY ' + @OrderByExpression
END

EXEC sp_executesql @SQL

GO

------------------------------------------------------------------------------------------------------------------------
-- Stored procedure name: [dbo].[p_WM_PhieuGDChiTiet_SelectAll]
-- Database: Warehouse_Management
-- Author: Ngo Thanh Tung
-- Time created: Sunday, November 04, 2012
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[p_WM_PhieuGDChiTiet_SelectAll]
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SELECT
	[ID],
	[Master_ID],
	[Barcode],
	[Ten],
	[DVT_ID],
	[SoLuong],
	[DonGia],
	[ThanhTien],
	[isHangTra]
FROM
	[dbo].[t_WM_PhieuGDChiTiet]	

GO

